using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using Sanchay;
namespace Sanchay
{

    public partial class SpareSalesRegister : Form
    {

        AutoCompleteStringCollection autoCompleteStrings;
        string temp = "";
        bool flag = false;
        bool invoiceFlag = false;
        bool _isEdit = false;
        // Variables to find if the form is unsaved or not
        string originalInvoiceNo = "";
        string originalCustomerName = "";
        string originalTaxesAndOtherCharges = "";
        string originalInvoiceDate = DateTime.Today.ToString("dd-MMM-yyyy");

        bool InvoiceAlreadyExists = false;
        bool isSaved = false;
        bool IsTransCommitable = true;

        public SpareSalesRegister()
        {
            InitializeComponent();
        }

        public SpareSalesRegister(bool isEdit, string invoiceNo)
        {
            _isEdit = isEdit;

            InitializeComponent();
            if (_isEdit)
            {
                DataAccess da = new DataAccess();
                string query = "SELECT InvoiceDate,Name,SubTotal,TaxesAndOtherCharges, TotalInvoiceValue from SpareSalesRegister  INNER JOIN " +
                     "Customer ON SpareSalesRegister.CustomerId = Customer.CustomerId " +
                     "WHERE InvoiceNo='" + invoiceNo + "'";
                DataTable dt = da.SelectCommand(query);
                originalInvoiceNo = txtSalesInvoiceNo.Text = invoiceNo;
                dateTimePicker1.Value = Convert.ToDateTime(dt.Rows[0][0]);
                originalInvoiceDate = dateTimePicker1.Value.ToString("dd-MMM-yyyy");
                originalCustomerName = txtCustomer.Text = dt.Rows[0][1].ToString();
                lblTotal.Text = dt.Rows[0][2].ToString();
                originalTaxesAndOtherCharges = txtTaxesAndCharges.Text = dt.Rows[0][3].ToString();
                lblGrandTotal.Text = dt.Rows[0][4].ToString();
                RefreshSummeryGrid();
                dgvSalesInvoice.Focus();

            }

        }

        private void BindSalesInvoiceGrid(string invoiceNo)
        {
            dgvSalesInvoice.AutoGenerateColumns = false;
            DataAccess da = new DataAccess();
            //string query = "SELECT [Parts No.], [Parts Name], [Quantity],[Unit Price], [Amount] from vw_SalesSummeryGrid WHERE InvoiceNo='" + invoiceNo + "'";
            string query = "SELECT * from vw_SalesInvoiceDetails WHERE SparePartsSalesInvoiceNo='" + invoiceNo + "'";
            DataTable dt = da.SelectCommand(query);


            dgvSalesInvoice.DataSource = dt;

            dgvSalesInvoice.Columns[0].DataPropertyName = "Date";
            dgvSalesInvoice.Columns[1].DataPropertyName = "Invoice No.";
            dgvSalesInvoice.Columns[2].DataPropertyName = "Amount";
            dgvSalesInvoice.Columns[3].DataPropertyName = "Customer";

            dgvSalesInvoice.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvSalesInvoice.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvSalesInvoice.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvSalesInvoice.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

            dgvSalesInvoice.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
            dgvSalesInvoice.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            dgvSalesInvoice.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            //dgvSalesInvoice.Columns[0].FillWeight = 4;
            dgvSalesInvoice.Columns[0].FillWeight = 17;
            dgvSalesInvoice.Columns[1].FillWeight = 45;
            dgvSalesInvoice.Columns[2].FillWeight = 8;
            dgvSalesInvoice.Columns[3].FillWeight = 15;
            dgvSalesInvoice.Columns[4].FillWeight = 15;
        }

        private void SpareSalesRegister_Load(object sender, EventArgs e)
        {
            dgvSalesInvoice.Rows[0].Cells[0].Value = "";
            dgvSalesInvoice.Rows[0].Cells[1].Value = "";
            dgvSalesInvoice.Rows[0].Cells[2].Value = "";
            dgvSalesInvoice.Rows[0].Cells[3].Value = "";
            dgvSalesInvoice.Rows[0].Cells[4].Value = "";
            dgvSalesInvoice.Rows[0].Cells[5].Value = "";

            autoCompleteStrings = new AutoCompleteStringCollection();
            autoCompleteStrings.Add("");
            AutoSuggestForCustomer();
        }

        private void ResetEntryGrid()
        {

            //for (int i = 0; i < 10; i++)
            //{
            //    dgvSalesInvoice.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            //    dgvSalesInvoice.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            //    dgvSalesInvoice.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            //    dgvSalesInvoice.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            //    dgvSalesInvoice.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

            //    dgvSalesInvoice.Rows.Add();
            //    //dgvSalesInvoice.Rows[i].Cells[0].Value = (_srNo).ToString();
            //    dgvSalesInvoice.Rows[i].Cells[0].Value = "";
            //    dgvSalesInvoice.Rows[i].Cells[1].Value = "";
            //    dgvSalesInvoice.Rows[i].Cells[2].Value = "";
            //    dgvSalesInvoice.Rows[i].Cells[3].Value = "";
            //    dgvSalesInvoice.Rows[i].Cells[4].Value = "";

            //    //_srNo += 1;
            //    if (dgvSalesInvoice.Rows.Count == 10)
            //        dgvSalesInvoice.AllowUserToAddRows = false;
            //}
            //dgvSalesInvoice.Rows[0].Cells[0].Selected = true; // Foucus to the first cell of the Grid
        }

        private void ClearEntryGrid()
        {
            try
            {
                int rowCount = dgvSalesInvoice.RowCount;
                for (int i = 0; i < rowCount; i++)
                {
                    dgvSalesInvoice.Rows.RemoveAt(0);

                }

                dgvSalesInvoice.AllowUserToAddRows = dgvSalesInvoice.RowCount <= 10;
            }
            catch (Exception ex)
            {

            }
        }

        private void ClearSummeryGrid()
        {

            dgvInvoiceSummery.DataSource = null;

        }

        private void RefreshSummeryGrid()
        {
            DataAccess da = new DataAccess();

            string query = "SELECT [Part Number], [Parts Name], [Quantity],[Unit Price], [Total], ID, InventoryId from vw_SalesSummeryGrid WHERE [Invoice Number]='" + txtSalesInvoiceNo.Text + "'";

            BindingSource bs = new BindingSource();

            DataTable dt = da.SelectCommand(query);
            dgvInvoiceSummery.DataSource = dt;
            dgvInvoiceSummery.Columns[0].DataPropertyName = "Part Number";
            dgvInvoiceSummery.Columns[1].DataPropertyName = "Parts Name";
            dgvInvoiceSummery.Columns[2].DataPropertyName = "Quantity";
            dgvInvoiceSummery.Columns[3].DataPropertyName = "Unit Price";
            dgvInvoiceSummery.Columns[4].DataPropertyName = "Total";
            dgvInvoiceSummery.Columns[5].DataPropertyName = "ID";
            dgvInvoiceSummery.Columns[5].Visible = false;
            dgvInvoiceSummery.Columns[6].DataPropertyName = "InventoryId";
            dgvInvoiceSummery.Columns[6].Visible = false;

            dt.AcceptChanges();

            decimal totalInvoiceValue = 0;

            for (int i = 0; i < dgvInvoiceSummery.Rows.Count; i++)
            {
                totalInvoiceValue += decimal.Parse(dgvInvoiceSummery.Rows[i].Cells[4].Value.ToString());
            }
            decimal tax = 0;
            if (txtTaxesAndCharges.Text == "")
                tax = 0;
            else
                tax = Convert.ToDecimal(txtTaxesAndCharges.Text);

            lblTotal.Text = General.ChangeIntoIndianCurrencyFormat(totalInvoiceValue);
            lblGrandTotal.Text = General.ChangeIntoIndianCurrencyFormat(tax + Convert.ToDecimal(lblTotal.Text.Replace(",", ""))).ToString();

            dgvInvoiceSummery.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvInvoiceSummery.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvInvoiceSummery.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvInvoiceSummery.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

            dgvInvoiceSummery.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
            dgvInvoiceSummery.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            dgvInvoiceSummery.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            //dgvInvoiceSummery.Columns[0].FillWeight = 4;
            dgvInvoiceSummery.Columns[0].FillWeight = 17;
            dgvInvoiceSummery.Columns[1].FillWeight = 45;
            dgvInvoiceSummery.Columns[2].FillWeight = 8;
            dgvInvoiceSummery.Columns[3].FillWeight = 15;
            dgvInvoiceSummery.Columns[4].FillWeight = 15;
            //GiveSerialNumbersToSummeryGrid();

        }

        private void AutoSuggestForCustomer()
        {
            // Auto Complete for Customer
            DataAccess da = new DataAccess();
            SqlConnection conn = da.conn;
            SqlCommand cmd = new SqlCommand("SELECT Name FROM Customer", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();


            while (dr.Read())
            {
                txtCustomer.AutoCompleteCustomSource.Add(dr.GetString(0));

            }
            txtCustomer.AutoCompleteMode = AutoCompleteMode.Suggest;
            txtCustomer.AutoCompleteSource = AutoCompleteSource.CustomSource;
            conn.Close();
        }

        private void dgvSalesInvoice_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            TextBox t = (TextBox)e.Control;
            t.TextChanged += new EventHandler(t_TextChanged);
        }

        void t_TextChanged(object sender, EventArgs e)
        {
            TextBox t = (TextBox)sender;


            if (t.Text.Length > 3)
            {
                return;
            }
            if (t.Text.Length == 3)
            {
                if (temp != t.Text)
                    flag = false;

                if (flag == false)
                {
                    SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
                    try
                    {

                        string query = "SELECT PartsNo FROM SpareParts WHERE PartsNo LIKE '" + t.Text + "%'";

                        SqlDataReader dr;
                        SqlCommand cmd = new SqlCommand(query, conn);
                        conn.Open();
                        dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            autoCompleteStrings.Add(dr.GetString(0));
                        }
                        t.AutoCompleteMode = AutoCompleteMode.Suggest;
                        t.AutoCompleteSource = AutoCompleteSource.CustomSource;
                        t.AutoCompleteCustomSource = autoCompleteStrings;
                        temp = t.Text;
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        //MessageBox.Show("An error occured. Please try again.");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

        }
        //}
        //private void dgvSalesInvoice_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        //{
        //    //
        //    //  Auto filling the Parts Name based on Parts No. column value
        //    //

        //    try
        //    {

        //        if (e.ColumnIndex == 0 && dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString() != "")
        //        {
        //            DataAccess da = new DataAccess();
        //            string partsNo = Convert.ToString(dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value);
        //            SqlConnection conn = da.conn;
        //            string query = "SELECT PartsName FROM SpareParts WHERE PartsNo='" + partsNo + "'";
        //            string query1 = "SELECT AvailableQty From vwCurrentStock WHERE PartsNo='" + partsNo + "'";
        //            DataTable dt = da.SelectCommand(query1);
        //            SqlDataReader dr;
        //            SqlCommand cmd = new SqlCommand(query, conn);
        //            autoCompleteStrings = new AutoCompleteStringCollection();

        //            conn.Open();
        //            dr = cmd.ExecuteReader();
        //            if (dr.HasRows == false)
        //            {
        //                //if (partsNo.Length >= 3)
        //                //{
        //                AddSpareParts objAddSpareParts = new AddSpareParts(dgvSalesInvoice, dgvSalesInvoice.CurrentRow.Index, partsNo);
        //                objAddSpareParts.ShowDialog();
        //                //}
        //            }
        //            while (dr.Read())
        //            {
        //                dgvSalesInvoice.Rows[e.RowIndex].Cells[1].Value = dr.GetString(0);
        //                dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value = dr.GetDecimal(1);

        //            }
        //            conn.Close();
        //            int availableQty = GetAvailableQty(da, partsNo);
        //            if (availableQty == -1)
        //            {
        //                availableQty = 0;
        //            }
        //            dgvSalesInvoice.Rows[e.RowIndex].Cells[2].Value = availableQty;
        //        }



        //        int itemQuantity = 0;
        //        decimal unitPrice = 0;
        //        decimal totalPrice = 0;

        //        if (e.ColumnIndex == 2 || e.ColumnIndex == 3 || e.ColumnIndex == 4)
        //        {
        //            if (Convert.ToString(dgvSalesInvoice.Rows[e.RowIndex].Cells[2].Value) != "")
        //            {
        //                itemQuantity = Convert.ToInt32(dgvSalesInvoice.Rows[e.RowIndex].Cells[2].Value);
        //            }

        //            if (Convert.ToString(dgvSalesInvoice.Rows[e.RowIndex].Cells[3].Value) != "")
        //            {
        //                unitPrice = Convert.ToDecimal(dgvSalesInvoice.Rows[e.RowIndex].Cells[3].Value);
        //                totalPrice = decimal.Round(itemQuantity * unitPrice, 2);
        //            }
        //            if (totalPrice == 0)
        //                dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value = "";
        //            else
        //                dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value = totalPrice;

        //            //int.TryParse(dgvSalesInvoice.Rows[e.RowIndex].Cells[2].Value.ToString(), out itemQuantity);
        //            //decimal.TryParse(dgvSalesInvoice.Rows[e.RowIndex].Cells[3].Value.ToString(), out unitPrice);
        //            //decimal.TryParse(dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value.ToString(), out totalAmount);

        //        }

        //    }
        //    catch (Exception ex)
        //    {

        //    }
        //    finally
        //    {

        //    }
        //}


        private void dgvSalesInvoice_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex == 0 && dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString() != "")
                {
                    DataAccess da = new DataAccess();
                    string partsNo = dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString();
                    //SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
                    SqlConnection conn = da.conn;

                    string query = "SELECT PartsName,CurrentMRP FROM SpareParts WHERE PartsNo='" + partsNo + "'";
                    string query1 = "SELECT [Available Qty] From vwCurrentStock WHERE [Parts No]='" + partsNo + "'";
                    DataTable dt = da.SelectCommand(query1);
                    SqlDataReader dr;
                    SqlCommand cmd = new SqlCommand(query, conn);
                    autoCompleteStrings = new AutoCompleteStringCollection();
                    conn.Open();
                    dr = cmd.ExecuteReader();
                    if (dr.HasRows == false)
                    {

                        AddSpareParts objAddSpareParts = new AddSpareParts(dgvSalesInvoice, dgvSalesInvoice.CurrentRow.Index, partsNo);
                        objAddSpareParts.ShowDialog();

                    }
                    while (dr.Read())
                    {
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[1].Value = dr.GetString(0);
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value = dr.GetDecimal(1);

                    }
                    conn.Close();
                    int availableQty = GetAvailableQty(da, partsNo);
                    if (availableQty == -1)
                    {
                        availableQty = 0;
                    }
                    dgvSalesInvoice.Rows[e.RowIndex].Cells[2].Value = availableQty;
                }
                int itemQuantity = 0;
                decimal unitPrice = 0;
                decimal totalPrice = 0;

                if (e.ColumnIndex == 3 || e.ColumnIndex == 4 || e.ColumnIndex == 5)
                {
                    if (Convert.ToString(dgvSalesInvoice.Rows[e.RowIndex].Cells[3].Value) != "")
                    {
                        itemQuantity = Convert.ToInt32(dgvSalesInvoice.Rows[e.RowIndex].Cells[3].Value);
                    }

                    if (Convert.ToString(dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value) != "")
                    {
                        unitPrice = Convert.ToDecimal(dgvSalesInvoice.Rows[e.RowIndex].Cells[4].Value);
                        totalPrice = decimal.Round(itemQuantity * unitPrice, 2);
                    }
                    if (totalPrice == 0)
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[5].Value = "";
                    else
                        dgvSalesInvoice.Rows[e.RowIndex].Cells[5].Value = totalPrice;

                }
            }
            catch (Exception ex)
            {

            }
        }


        private bool CheckDuplicateEntry(string partsNo)
        {
            int count = 0;
            for (int i = 0; i < dgvSalesInvoice.Rows.Count; i++)
            {
                if (dgvSalesInvoice.Rows[i].Cells[0].Value.ToString() != "")
                    count += 1;


            }
            for (int j = 0; j < count - 1; j++)
            {
                if (dgvSalesInvoice.Rows[j].Cells[0].Value.ToString() == partsNo)
                    return true;
            }
            return false;
        }

        // Save Button
        private void pictureBox2_Click_1(object sender, EventArgs e)
        {
            if (ValidateForm())
            {
                DataAccess da = new DataAccess();
                da.openConnection();
                SqlTransaction trans = da.conn.BeginTransaction();
                AddCustomerIfNotExists();
                InsertUpdateSalesInvoiceDetails(da, trans);
                UpdateInventory(da,trans); // Insert Into Inventory Table if entry grid is not empty
                UpdateExistingRecords(da,trans);
                try
                {
                    if (IsTransCommitable)
                        trans.Commit();
                    else
                        trans.Rollback();
                }
                catch { }
                finally
                {
                    da.conn.Close();
                }
                RefreshSummeryGrid();
                ClearEntryGrid();
                ResetEntryGrid();

                originalInvoiceNo = txtSalesInvoiceNo.Text;
                originalCustomerName = txtCustomer.Text;
                originalTaxesAndOtherCharges = txtTaxesAndCharges.Text;
                originalInvoiceDate = dateTimePicker1.Value.ToString("dd-MMM-yyyy");
            }
        }

        private void pbNewSalesInvoice_Click(object sender, EventArgs e)
        {
            DialogResult dr;
            if (CheckIfFormIsUnsaved())
            {
                dr = MessageBox.Show("Do you want to save changes?", "Save Changes", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
                if (dr == DialogResult.Yes)
                {
                    pictureBox2_Click_1(sender, e);
                    if (ValidateForm())
                        SetNewEntryForm();
                }
                else if (dr == DialogResult.No)
                    SetNewEntryForm();
            }
            else
                SetNewEntryForm();
        }

        private void SetNewEntryForm()
        {
            ClearSummeryGrid();
            ClearEntryGrid();
            //ResetEntryGrid();
            txtSalesInvoiceNo.Text = "";
            dateTimePicker1.ResetText();
            txtCustomer.Text = "";
            invoiceFlag = false;
            lblTotal.Text = "0";
            txtTaxesAndCharges.Text = "";
            lblGrandTotal.Text = "0";
            _isEdit = false;
            InvoiceAlreadyExists = false;
            originalInvoiceNo = "";
            originalCustomerName = "";
            originalTaxesAndOtherCharges = "";
            originalInvoiceDate = DateTime.Today.ToString("dd-MMM-yyyy");
        }

        private bool CheckIfFormIsUnsaved()
        {
            txtSalesInvoiceNo.Focus();
            if (txtSalesInvoiceNo.Text != originalInvoiceNo)
                return true;
            if (dateTimePicker1.Value.ToString("dd-MMM-yyyy") != originalInvoiceDate)
                return true;
            if (txtCustomer.Text != originalCustomerName)
                return true;
            if (dgvSalesInvoice.Rows.Count > 1)
                return true;

            DataTable dt = (DataTable)dgvInvoiceSummery.DataSource;
            int editedRowsCount = 0;

            if (dt == null || dt.GetChanges() == null)
                return false;
            else
            {
                editedRowsCount = dt.GetChanges().Rows.Count;
                if (editedRowsCount > 0)
                    return true;
                else
                    return false;
            }

        }

        private void UpdateExistingRecords(DataAccess da,SqlTransaction trans)
        {
            DataTable dt = (DataTable)dgvInvoiceSummery.DataSource;
            int editedRowsCount = 0;
            if (dt == null || dt.GetChanges() == null)
                return;
            else
            {
                editedRowsCount = dt.GetChanges().Rows.Count;


                string query;
                int rowsAffacted = 0;
                decimal totalInvoiceValue = 0;
                decimal tax = 0;
                if (txtTaxesAndCharges.Text == "")
                    tax = 0;
                else
                    tax = Convert.ToDecimal(txtTaxesAndCharges.Text);
                for (int i = 0; i < dgvInvoiceSummery.Rows.Count; i++)
                {
                    totalInvoiceValue += decimal.Parse(dgvInvoiceSummery.Rows[i].Cells[4].Value.ToString());
                }

                totalInvoiceValue += tax;
                for (int i = 0; i < editedRowsCount; i++)
                {
                    string partsNo = dt.GetChanges().Rows[i]["Part Number", DataRowVersion.Original].ToString();
                    int originalQty = Convert.ToInt32(dt.GetChanges().Rows[i]["Quantity", DataRowVersion.Original]);
                    // Update Sales Invoice Item Table
                    if (dt.GetChanges().Rows[i].RowState == DataRowState.Deleted)
                    {
                        //SqlParameter[] param = new SqlParameter[5];
                        //param[0] = new SqlParameter("ItemQuantity", Convert.ToInt32(dt.GetChanges().Rows[i]["Quantity", DataRowVersion.Original]));
                        //param[1] = new SqlParameter("SparePartsSalesInvoiceNo", txtSalesInvoiceNo.Text);
                        //param[2] = new SqlParameter("PartsNo", dt.GetChanges().Rows[i]["Part Number", DataRowVersion.Original].ToString());
                        //param[3] = new SqlParameter("PartsUnitPrice", Convert.ToDecimal(dt.GetChanges().Rows[i]["Unit Price", DataRowVersion.Original]));
                        //param[4] = new SqlParameter("PartTotalPrice", Convert.ToDecimal(dt.GetChanges().Rows[i]["Total", DataRowVersion.Original]));

                        //query = "DELETE FROM SparePartsSalesDetails WHERE ID=" + dt.GetChanges().Rows[i]["ID", DataRowVersion.Original];
                        //rowsAffacted = da.DoCommand(query, param);

                        /// Update Inventory
                        int availableQty = GetAvailableQty(da, partsNo, trans);
                        if (availableQty != 0)// If not Null
                        {
                            query = "UPDATE InventoryMaster SET isCurrent=0 WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
                            if (da.DoCommand(query, trans) == 0)
                                IsTransCommitable = false;
                        }

                        int currentQty = Convert.ToInt32(dt.GetChanges().Rows[i]["Quantity", DataRowVersion.Original]);
                        SqlParameter[] param1 = new SqlParameter[9];
                        param1[0] = new SqlParameter("PartsNo", partsNo);
                        param1[1] = new SqlParameter("Qty", Convert.ToInt32(dt.GetChanges().Rows[i]["Quantity", DataRowVersion.Original]));
                        param1[2] = new SqlParameter("AvailableQty", availableQty + originalQty);
                        param1[3] = new SqlParameter("TransactionDate", DateTime.Now);
                        param1[4] = new SqlParameter("isCurrent", 1);
                        param1[5] = new SqlParameter("TransactionType", Enums.TransactionType.SalesItemDelete);
                        param1[6] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
                        param1[7] = new SqlParameter("UnitPrice", Convert.ToInt32(dt.GetChanges().Rows[i]["Unit Price", DataRowVersion.Original]));
                        param1[8] = new SqlParameter("TotalPrice", Convert.ToInt32(dt.GetChanges().Rows[i]["Total", DataRowVersion.Original]));

                        query = "DELETE FROM InventoryMaster WHERE InventoryId=" + dt.GetChanges().Rows[i]["InventoryId", DataRowVersion.Original];
                        ////query = "INSERT INTO InventoryMaster (PartsNo, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                        if (da.DoCommand(query, trans) == 0)
                            IsTransCommitable = false;

                        //query = "UPDATE InventoryMaster SET isCurrent=1 WHERE InventoryId IN (SELECT TOP 1 InventoryId FROM InventoryMaster" + 
                        //        " WHERE PartsNo='" + partsNo + "' ORDER BY InventoryId DESC)";
                        //if (da.DoCommand(query, trans) == 0)
                        //    IsTransCommitable = false;

                        query = "INSERT INTO InventoryMaster (PartsNO, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                        if (da.DoCommand(query, param1, trans) == 0)
                            IsTransCommitable = false;
                    }
                    else
                    {
                        SqlParameter[] param = new SqlParameter[5];
                        param[0] = new SqlParameter("ItemQuantity", Convert.ToInt32(dt.GetChanges().Rows[i]["Quantity"]));
                        param[1] = new SqlParameter("SparePartsSalesInvoiceNo", txtSalesInvoiceNo.Text);
                        param[2] = new SqlParameter("PartsNo", dt.GetChanges().Rows[i]["Part Number"].ToString());
                        param[3] = new SqlParameter("PartsUnitPrice", Convert.ToDecimal(dt.GetChanges().Rows[i]["Unit Price"]));
                        param[4] = new SqlParameter("PartTotalPrice", Convert.ToDecimal(dt.GetChanges().Rows[i]["Total"]));

                        query = "UPDATE SparePartsSalesDetails SET ItemQuantity=@ItemQuantity, SparePartsSalesInvoiceNo=@SparePartsSalesInvoiceNo, PartsNo=@PartsNo, PartsUnitPrice= @PartsUnitPrice,PartTotalPrice=@PartTotalPrice" +
                            " WHERE ID=" + dt.GetChanges().Rows[i]["ID"];
                        if (da.DoCommand(query, param, trans) == 0)
                            IsTransCommitable = false;

                        /// Update Inventory
                        int availableQty = GetAvailableQty(da, partsNo,trans);
                        if (availableQty != 0)// If not Null
                        {
                            query = "UPDATE InventoryMaster SET isCurrent=0 WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
                            if (da.DoCommand(query, trans) == 0)
                                IsTransCommitable = false;
                        }
                     
                        int currentQty = Convert.ToInt32(dt.GetChanges().Rows[i]["Quantity"]);
                        SqlParameter[] param2 = new SqlParameter[9];
                        param2[0] = new SqlParameter("PartsNo", partsNo);
                        param2[1] = new SqlParameter("Qty", currentQty);
                        param2[2] = new SqlParameter("AvailableQty", availableQty - (currentQty - originalQty));
                        param2[3] = new SqlParameter("TransactionDate", DateTime.Now);
                        param2[4] = new SqlParameter("isCurrent", 1);
                        param2[5] = new SqlParameter("TransactionType", Enums.TransactionType.Sales);
                        param2[6] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
                        param2[7] = new SqlParameter("UnitPrice", Convert.ToInt32(dt.GetChanges().Rows[i]["Unit Price", DataRowVersion.Original]));
                        param2[8] = new SqlParameter("TotalPrice", Convert.ToInt32(dt.GetChanges().Rows[i]["Total", DataRowVersion.Original]));

                        query = "UPDATE InventoryMaster SET PartsNo =@PartsNo , Qty= @Qty, AvailableQty = @AvailableQty, TransactionDate = @TransactionDate ,isCurrent = @isCurrent," +
                            " TransactionType = @TransactionType,InvoiceNo = @InvoiceNo,UnitPrice = @UnitPrice ,TotalPrice= @TotalPrice" + 
                            " WHERE InventoryId=" + Convert.ToInt32(dt.GetChanges().Rows[i]["InventoryId", DataRowVersion.Original]);
                        if (da.DoCommand(query, param2, trans) == 0)
                            IsTransCommitable = false;

                    }
                }
            }

        }

        private bool ValidateForm()
        {
            txtSalesInvoiceNo.Focus();
            bool flag = false;
            // Invoice Number
            if (txtSalesInvoiceNo.Text == "")
            {
                txtSalesInvoiceNo.BackColor = Color.Pink;
                return false;
            }

            // Validate Entry Grid
            int columnCount = dgvSalesInvoice.ColumnCount;

            int count = dgvSalesInvoice.Rows.Count;

            for (int i = 0; i < dgvSalesInvoice.Rows.Count; i++)
            {

                bool emptyRow = CheckIfRowIsEmpty(i);

                for (int j = 0; j < columnCount; j++)
                {
                    bool IsNumeric = true;


                    if (j == 3 || j == 4 || j == 5) // Validate Numeric
                    {
                        IsNumeric = Validation.CheckIfNumeric(dgvSalesInvoice.Rows[i].Cells[j].Value);
                    }

                    bool IsEmpty = Validation.CheckIfEmpty(dgvSalesInvoice.Rows[i].Cells[j].Value);
                    bool IsNull = Validation.CheckIfNull(dgvSalesInvoice.Rows[i].Cells[j].Value);

                    if (!emptyRow)
                    {
                        if (IsNull || IsEmpty || !IsNumeric)
                        {
                            dgvSalesInvoice.Rows[i].Cells[j].Style.BackColor = Color.Pink;
                            flag = true;

                        }
                        else
                            dgvSalesInvoice.Rows[i].Cells[j].Style.BackColor = Color.White;
                    }


                }

                if (emptyRow)
                {
                    if (i != dgvSalesInvoice.Rows.Count)
                    {
                        try
                        {

                            dgvSalesInvoice.Rows.RemoveAt(i);
                            --i;
                        }
                        catch (Exception e)
                        {
                            dgvSalesInvoice.Rows[i].Cells[0].Style.BackColor = Color.White;
                            dgvSalesInvoice.Rows[i].Cells[1].Style.BackColor = Color.White;
                            dgvSalesInvoice.Rows[i].Cells[2].Style.BackColor = Color.White;
                            dgvSalesInvoice.Rows[i].Cells[3].Style.BackColor = Color.White;
                            dgvSalesInvoice.Rows[i].Cells[4].Style.BackColor = Color.White;
                            dgvSalesInvoice.Rows[i].Cells[5].Style.BackColor = Color.White;
                        }

                    }

                }
            }

            if (flag == true)
            {
                return false;
            }
            return true;
        }

        private bool CheckIfRowIsEmpty(int i)
        {
            //if (dgvSalesInvoice.Rows[i].Cells[0].Value == null)
            //    dgvSalesInvoice.Rows[i].Cells[0].Value = "";
            //if (dgvSalesInvoice.Rows[i].Cells[1].Value == null)
            //    dgvSalesInvoice.Rows[i].Cells[1].Value = "";
            //if (dgvSalesInvoice.Rows[i].Cells[2].Value == null)
            //    dgvSalesInvoice.Rows[i].Cells[2].Value = "";
            //if (dgvSalesInvoice.Rows[i].Cells[3].Value == null)
            //    dgvSalesInvoice.Rows[i].Cells[3].Value = "";
            //if (dgvSalesInvoice.Rows[i].Cells[4].Value == null)
            //    dgvSalesInvoice.Rows[i].Cells[4].Value = "";

            if (Convert.ToString(dgvSalesInvoice.Rows[i].Cells[0].Value) == "" && Convert.ToString(dgvSalesInvoice.Rows[i].Cells[1].Value) == "" && Convert.ToString(dgvSalesInvoice.Rows[i].Cells[2].Value) == "" && Convert.ToString(dgvSalesInvoice.Rows[i].Cells[3].Value) == "" && Convert.ToString(dgvSalesInvoice.Rows[i].Cells[4].Value) == "")
            {
                return true;
            }
            return false;
        }

        private void AddCustomerIfNotExists()
        {
            DataAccess da = new DataAccess();
            bool isCustomerExists = CheckIfCustomerExists(da);
            if (isCustomerExists == false)
            {
                string query = "INSERT INTO Customer values ('" + txtCustomer.Text + "' , '', '')";
                try
                {
                    if (txtCustomer.Text != "")
                    {
                        da.DoCommand(query);
                    }
                }
                catch (Exception ex)
                {

                }

            }
        }

        /// <summary>
        /// Checking if Customer exists
        /// </summary>
        private bool CheckIfCustomerExists(DataAccess da)
        {
            bool isCustomerExists;
            string query = "SELECT Name FROM Customer WHERE Name ='" + txtCustomer.Text + "'";
            DataTable dt = da.SelectCommand(query);
            if (dt.Rows.Count != 0)
            {
                isCustomerExists = true;
            }
            else
            {
                isCustomerExists = false;
            }
            return isCustomerExists;
        }

        private void InsertUpdateSalesInvoiceDetails(DataAccess da,SqlTransaction trans)
        {
            int rowsAffacted = 0;
            string query, query1;
            decimal tax = 0;
            if (txtTaxesAndCharges.Text == "")
                tax = 0;
            else
                tax = Convert.ToDecimal(txtTaxesAndCharges.Text);
            // SpareParts Sales Invoice No
            SqlParameter[] param = new SqlParameter[5];
            param[0] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
            param[1] = new SqlParameter("InvoiceDate", dateTimePicker1.Value.ToShortDateString());
            param[2] = new SqlParameter("CustomerId", GetCustomerIdByName(da, txtCustomer.Text, trans));
            param[3] = new SqlParameter("SubTotal", Convert.ToDecimal(lblTotal.Text));
            param[4] = new SqlParameter("TaxesAndOtherCharges", tax);

            if (_isEdit)
                InvoiceAlreadyExists = true;
            if (!InvoiceAlreadyExists)
            {
                query = "INSERT INTO SpareSalesRegister (InvoiceNo, InvoiceDate, CustomerId,SubTotal,TaxesAndOtherCharges ) VALUES (@InvoiceNo, @InvoiceDate, @CustomerId,@SubTotal, @TaxesAndOtherCharges)";
                if (da.DoCommand(query, param, trans) == 0)
                    IsTransCommitable = false;
                else
                    InvoiceAlreadyExists = true;
            }
            else
            {
                query = "UPDATE SpareSalesRegister SET InvoiceNo=@InvoiceNo, InvoiceDate=@InvoiceDate, CustomerId=@CustomerId,SubTotal=@SubTotal,TaxesAndOtherCharges =@TaxesAndOtherCharges" +
                    " WHERE InvoiceNo='" + originalInvoiceNo + "'";
                //query1 = "UPDATE SparePartsSalesDetails SET SparePartsSalesInvoiceNo = '" + txtSalesInvoiceNo.Text + "'" +
                //    " WHERE SparePartsSalesInvoiceNo='" + originalInvoiceNo + "'";
                if (da.DoCommand(query, param, trans) == 0)
                    IsTransCommitable = false;
                //da.DoCommand(query1);

            }
            originalInvoiceNo = txtSalesInvoiceNo.Text;

        }

        /// <summary>
        /// Update Inventory
        /// </summary>
        /// <param name="da"></param>
        private void UpdateInventory(DataAccess da, SqlTransaction trans)
        {
            string query;
            int inventoryId = 0;
            try
            {
                decimal totalInvoiceValue = 0;
                decimal tax = 0;
                if (txtTaxesAndCharges.Text == "")
                    tax = 0;
                else
                    tax = Convert.ToDecimal(txtTaxesAndCharges.Text);
                for (int i1 = 0; i1 < dgvInvoiceSummery.Rows.Count; i1++)
                {
                    totalInvoiceValue += decimal.Parse(dgvInvoiceSummery.Rows[i1].Cells[4].Value.ToString());
                }
                totalInvoiceValue += tax;

                for (int i = 0; i < dgvSalesInvoice.Rows.Count; i++)
                {
                    if (dgvSalesInvoice.Rows[i].Cells[0].Value.ToString() != "")
                    {
                        int currentQty = Convert.ToInt16(dgvSalesInvoice.Rows[i].Cells[3].Value);
                        int rowsAffacted = 0;
                        if (dgvSalesInvoice.Rows[i].Cells[0].Value.ToString() != "")
                        {
                            string partsNo = dgvSalesInvoice.Rows[i].Cells[0].Value.ToString();
                            int availableQty = GetAvailableQty(da, partsNo,trans);
                            if (availableQty != 0)
                            {
                                query = "UPDATE InventoryMaster SET isCurrent=0 WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
                                if (da.DoCommand(query, trans) == 0)
                                    IsTransCommitable = false;
                            }
                            
                            // Inventory
                            SqlParameter[] param = new SqlParameter[9];
                            param[0] = new SqlParameter("PartsNo", partsNo);
                            param[1] = new SqlParameter("Qty", currentQty);
                            param[2] = new SqlParameter("AvailableQty", availableQty - currentQty);
                            param[3] = new SqlParameter("TransactionDate", dateTimePicker1.Value);
                            param[4] = new SqlParameter("isCurrent", 1);
                            param[5] = new SqlParameter("TransactionType", Enums.TransactionType.Sales);
                            param[6] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
                            param[7] = new SqlParameter("UnitPrice", Convert.ToDouble(dgvSalesInvoice.Rows[i].Cells[4].Value));
                            param[8] = new SqlParameter("TotalPrice", Convert.ToDouble(dgvSalesInvoice.Rows[i].Cells[5].Value));

                            query = "INSERT INTO InventoryMaster (PartsNO, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                            if (da.DoCommand(query, param, trans) == 0)
                                IsTransCommitable = false;

                            string query1 = "SELECT TOP 1 InventoryId FROM InventoryMaster ORDER BY InventoryId DESC ";
                            inventoryId = (int)da.SelectCommand(query1,trans).Rows[0][0];

                          
                            totalInvoiceValue += Convert.ToDecimal(dgvSalesInvoice.Rows[i].Cells[5].Value);

                            // Sales Invoice Item Details
                            SqlParameter[] param1 = new SqlParameter[6];
                            param1[0] = new SqlParameter("ItemQuantity", Convert.ToInt32(dgvSalesInvoice.Rows[i].Cells[3].Value));
                            param1[1] = new SqlParameter("SparePartsSalesInvoiceNo", txtSalesInvoiceNo.Text);
                            param1[2] = new SqlParameter("PartsNo", dgvSalesInvoice.Rows[i].Cells[0].Value.ToString());
                            param1[3] = new SqlParameter("PartsUnitPrice", Convert.ToDecimal(dgvSalesInvoice.Rows[i].Cells[4].Value));
                            param1[4] = new SqlParameter("PartTotalPrice", Convert.ToDecimal(dgvSalesInvoice.Rows[i].Cells[5].Value));
                            param1[5] = new SqlParameter("InventoryId", inventoryId);

                            query = "INSERT INTO SparePartsSalesDetails (ItemQuantity, SparePartsSalesInvoiceNo, PartsNo, PartsUnitPrice,PartTotalPrice,InventoryId) VALUES (@ItemQuantity, @SparePartsSalesInvoiceNo, @PartsNo, @PartsUnitPrice,@PartTotalPrice,@InventoryId)";
                            if (da.DoCommand(query, param1, trans) == 0)
                                IsTransCommitable = false;


                        }

                    }
                }

                query = "UPDATE SpareSalesRegister SET TotalInvoiceValue = " + totalInvoiceValue + ", TaxesAndOtherCharges = " + tax + " WHERE InvoiceNo='" + txtSalesInvoiceNo.Text + "'";
                if (da.DoCommand(query, trans) == 0)
                    IsTransCommitable = false;
               
            }

            catch (Exception ex)
            {
               
            }

        }

        private void GiveSerialNumbersToSummeryGrid()
        {
            int count = 0;
            foreach (DataGridViewRow dr in dgvInvoiceSummery.Rows)
            {
                count += 1;
                if (dgvInvoiceSummery.Rows.Count >= count)
                {
                    dr.Cells[0].Value = count;
                }
            }
        }

        /// <summary>
        /// Inserting Spare Sales Item details from the grid row by row.
        /// </summary>
        /// <param name="da">DataAccess class</param>
        private void InsertSalesInvoiceItemDetails(DataAccess da, int inventoryId)
        {
            try
            {
                string query;
                int rowsAffacted = 0;
                decimal totalInvoiceValue = 0;
                decimal tax = 0;
                if (txtTaxesAndCharges.Text == "")
                    tax = 0;
                else
                    tax = Convert.ToDecimal(txtTaxesAndCharges.Text);
                for (int i = 0; i < dgvInvoiceSummery.Rows.Count; i++)
                {
                    totalInvoiceValue += decimal.Parse(dgvInvoiceSummery.Rows[i].Cells[4].Value.ToString());
                }
                totalInvoiceValue += tax;
                for (int i = 0; i < dgvSalesInvoice.Rows.Count; i++)
                {
                   
                        totalInvoiceValue += Convert.ToDecimal(dgvSalesInvoice.Rows[i].Cells[5].Value);

                        // Sales Invoice Item Details
                        SqlParameter[] param = new SqlParameter[6];
                        param[0] = new SqlParameter("ItemQuantity", Convert.ToInt32(dgvSalesInvoice.Rows[i].Cells[3].Value));
                        param[1] = new SqlParameter("SparePartsSalesInvoiceNo", txtSalesInvoiceNo.Text);
                        param[2] = new SqlParameter("PartsNo", dgvSalesInvoice.Rows[i].Cells[0].Value.ToString());
                        param[3] = new SqlParameter("PartsUnitPrice", Convert.ToDecimal(dgvSalesInvoice.Rows[i].Cells[4].Value));
                        param[4] = new SqlParameter("PartTotalPrice", Convert.ToDecimal(dgvSalesInvoice.Rows[i].Cells[5].Value));
                        param[5] = new SqlParameter("InventoryId", inventoryId);

                        query = "INSERT INTO SparePartsSalesDetails (ItemQuantity, SparePartsSalesInvoiceNo, PartsNo, PartsUnitPrice,PartTotalPrice,InventoryId) VALUES (@ItemQuantity, @SparePartsSalesInvoiceNo, @PartsNo, @PartsUnitPrice,@PartTotalPrice,@InventoryId)";
                        rowsAffacted = da.DoCommand(query, param);
                   
                }

                query = "UPDATE SpareSalesRegister SET TotalInvoiceValue = " + totalInvoiceValue + ", TaxesAndOtherCharges = " + tax + " WHERE InvoiceNo='" + txtSalesInvoiceNo.Text + "'";
                da.DoCommand(query);
            }
            catch (Exception e)
            {

            }


        }

        private int GetAvailableQty(DataAccess da, string partsNo)
        {
            string query = "SELECT AvailableQty FROM InventoryMaster WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
            DataTable dt = da.SelectCommand(query);
            if (dt.Rows.Count != 0)
            {
                return (int)dt.Rows[0][0];
            }
            else
            {
                return 0;
            }
        }

        private int GetAvailableQty(DataAccess da, string partsNo,SqlTransaction trans)
        {
            string query = "SELECT AvailableQty FROM InventoryMaster WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
            DataTable dt = da.SelectCommand(query,trans);
            if (dt.Rows.Count != 0)
            {
                return (int)dt.Rows[0][0];
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// If Parts No. doesn't exists then adding it to the database on Row leave event
        /// </summary>
        //private void dgvSalesInvoice_RowLeave(object sender, DataGridViewCellEventArgs e)
        //{
        //    DataAccess da = new DataAccess();
        //    string partsNo = dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString();

        //    if (partsNo != "")      // If Parts No. column is not empty
        //    {
        //        string query = "SELECT PartsName FROM SpareParts WHERE PartsNo='" + partsNo + "'";

        //        SqlDataReader dr;
        //        SqlCommand cmd = new SqlCommand(query, da.conn);
        //        da.conn.Open();
        //        dr = cmd.ExecuteReader();
        //        if (dr.HasRows == false)
        //        {
        //            dr.Close();
        //            SqlParameter[] param = new SqlParameter[4];
        //            param[0] = new SqlParameter("PartsNo", dgvSalesInvoice.Rows[e.RowIndex].Cells[0].Value.ToString());
        //            param[1] = new SqlParameter("PartsName", dgvSalesInvoice.Rows[e.RowIndex].Cells[1].Value.ToString());
        //            param[2] = new SqlParameter("Description", "");
        //            param[3] = new SqlParameter("BikeId", 1);

        //            string query1 = "INSERT INTO SpareParts (PartsNo, PartsName, Description, BikeId) VALUES (@PartsNo, @PartsName, @Description, @BikeId)";
        //            cmd = new SqlCommand(query1, da.conn);
        //            cmd.Parameters.AddRange(param);
        //            cmd.ExecuteNonQuery();
        //        }
        //    }

        //}
        //
        // Close Button
        //
        private void pictureBox1_Click(object sender, EventArgs e)
        {
            this.Close();

        }

        private void txtSalesInvoiceNo_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                CheckDuplicateInvoice();

        }

        /// <summary>
        /// Checking Duplicate Sales Invoice, If exists then not allow proceeding
        /// </summary>
        private void CheckDuplicateInvoice()
        {
            if (txtSalesInvoiceNo.Text != "")
            {
                DataAccess da = new DataAccess();
                string query = "SELECT InvoiceNo FROM SpareSalesRegister WHERE InvoiceNo='" + txtSalesInvoiceNo.Text + "'";
                SqlCommand cmd = new SqlCommand(query, da.conn);
                da.conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.HasRows == true)
                {
                    da.conn.Close();
                    //txtSalesInvoiceNo.Text = "";
                    //txtSalesInvoiceNo.Focus();

                    DataTable dt = FillCustomerNameAndInvoiceDate(da, txtSalesInvoiceNo.Text);
                    txtCustomer.Text = (string)dt.Rows[0][2];
                    dateTimePicker1.Value = (DateTime)dt.Rows[0][0];

                    //InsertSalesInvoiceItemDetails(da);
                    RefreshSummeryGrid();

                    originalInvoiceNo = txtSalesInvoiceNo.Text;
                    originalCustomerName = txtCustomer.Text;
                    originalTaxesAndOtherCharges = txtTaxesAndCharges.Text;
                    originalInvoiceDate = dateTimePicker1.Value.ToString("dd-MMM-yyyy");
                    // Showing Invoice Item Detail summery (saved only) into another grid
                    _isEdit = true;

                    //ClearEntryGrid();
                    //ResetEntryGrid();
                }
            }
            //else
            //{
            //    MessageBox.Show("Please enter Invoice No.");
            //    txtSalesInvoiceNo.Focus();
            //}

        }

        private DataTable FillCustomerNameAndInvoiceDate(DataAccess da, string invoiceNo)
        {
            string query = "SELECT SpareSalesRegister.InvoiceDate, "
                            + "SpareSalesRegister.CustomerId, Customer.Name "
                            + "FROM SpareSalesRegister INNER JOIN "
                            + "Customer ON SpareSalesRegister.CustomerId = Customer.CustomerId "
                            + "WHERE SpareSalesRegister.InvoiceNo='" + invoiceNo + "'";
            DataTable dt = da.SelectCommand(query);
            return dt;

        }

        private int GetCustomerIdByName(DataAccess da, string CustomerName)
        {
            if (txtCustomer.Text != "")
            {
                string query = "SELECT CustomerId FROM Customer WHERE Name='" + CustomerName + "'";
                DataTable dt = da.SelectCommand(query);
                return (int)dt.Rows[0][0];
            }
            return 1;
        }

        private int GetCustomerIdByName(DataAccess da, string CustomerName, SqlTransaction trans)
        {
            if (txtCustomer.Text != "")
            {
                string query = "SELECT CustomerId FROM Customer WHERE Name='" + CustomerName + "'";
                DataTable dt = da.SelectCommand(query,trans);
                return (int)dt.Rows[0][0];
            }
            return 1;
        }

        private void txtSalesInvoiceNo_Leave(object sender, EventArgs e)
        {
            txtSalesInvoiceNo.Text.ToUpper();
        }

        private void txtSalesInvoiceNo_TextChanged(object sender, EventArgs e)
        {
            if (txtSalesInvoiceNo.Text.Length > 0)
            {
                txtSalesInvoiceNo.BackColor = Color.White;
            }
        }

        private void dgvSalesInvoice_UserAddedRow(object sender, DataGridViewRowEventArgs e)
        {
            dgvSalesInvoice.Rows[e.Row.Index].Cells[0].Value = "";
            dgvSalesInvoice.Rows[e.Row.Index].Cells[1].Value = "";
            dgvSalesInvoice.Rows[e.Row.Index].Cells[2].Value = "";
            dgvSalesInvoice.Rows[e.Row.Index].Cells[3].Value = "";
            dgvSalesInvoice.Rows[e.Row.Index].Cells[4].Value = "";

            dgvSalesInvoice.AllowUserToAddRows = dgvSalesInvoice.RowCount <= 10;
        }

        private void dgvSalesInvoice_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
        {

        }

        private void dgvSalesInvoice_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            bool Handle = false;
            if (dgvSalesInvoice.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.Equals(DBNull.Value))
            {
                Handle = true;
            }
            else
            {
                Handle = false;
            }
            e.Cancel = Handle;
        }

        private void txtTaxesAndCharges_TextChanged_1(object sender, EventArgs e)
        {
            if (txtTaxesAndCharges.Text != "")
                lblGrandTotal.Text = General.ChangeIntoIndianCurrencyFormat((Convert.ToDecimal(lblTotal.Text.Replace(",", "")) + Convert.ToDecimal(txtTaxesAndCharges.Text)));
            else
                lblGrandTotal.Text = General.ChangeIntoIndianCurrencyFormat((Convert.ToDecimal(lblTotal.Text.Replace(",", "")) + 0));
        }

        private void txtCustomer_Leave(object sender, EventArgs e)
        {
            if (txtCustomer.Text == "")
                txtCustomer.Text = "CASH";
            if (txtCustomer.Text.Length >= 3)
            {
                DataAccess da = new DataAccess();
                if (!CheckIfCustomerExists(da))
                {
                    AddCustomer oAddCustomer = new AddCustomer(txtCustomer);
                    oAddCustomer.ShowDialog();
                }
            }
        }

        private void dgvInvoiceSummery_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dgvInvoiceSummery_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            TextBox t = (TextBox)e.Control;
            t.TextChanged += new EventHandler(t_TextChanged);
        }

        private void SpareSalesRegister_KeyDown_1(object sender, KeyEventArgs e)
        {
            if (e.Modifiers == Keys.Control)
            {
                switch (e.KeyCode)
                {
                    case Keys.S:
                        pictureBox2_Click_1(sender, e);
                        break;
                    case Keys.N:
                        pbNewSalesInvoice_Click(sender, e);
                        break;
                    case Keys.E:
                        pictureBox1_Click(sender, e);
                        break;
                    case Keys.D:
                        pbDelete_Click(sender, e);
                        break;
                }
            }
        }

        private void txtSalesInvoiceNo_Leave_1(object sender, EventArgs e)
        {
            txtSalesInvoiceNo.Text = txtSalesInvoiceNo.Text.ToUpper();
            CheckDuplicateInvoice();

        }

        private void SpareSalesRegister_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (CheckIfFormIsUnsaved())
            {
                DialogResult dr;
                if (CheckIfFormIsUnsaved())
                {
                    dr = MessageBox.Show("Do you want to save the changes?", "Save Changes?", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.Yes)
                    {
                        pictureBox2_Click_1(sender, e);
                        if (ValidateForm())
                            this.Close();
                        else
                        {
                            e.Cancel = true;
                            return;
                        }
                    }
                    else if (dr == DialogResult.Cancel)
                    {
                        e.Cancel = true;
                        return;
                    }

                }

            }

            // Show Register window if already opened
            Registers Registers;
            if (General.isOpened("Registers"))
            {
                Registers = (Registers)Application.OpenForms["Registers"];
                Registers.Close();
                Registers = new Registers();
                Registers.MdiParent = DashBoard.ActiveForm;
                Registers.Show();
                Registers.ActivateSales(sender, e);
            }

        }

        private void dgvInvoiceSummery_CellEndEdit_1(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex == 0 && dgvInvoiceSummery.Rows[e.RowIndex].Cells[0].Value.ToString() != "")
                {
                    SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
                    string partsNo = Convert.ToString(dgvInvoiceSummery.Rows[e.RowIndex].Cells[0].Value);
                    string query = "SELECT PartsName FROM SpareParts WHERE PartsNo='" + partsNo + "'";
                    SqlDataReader dr;
                    SqlCommand cmd = new SqlCommand(query, conn);
                    autoCompleteStrings = new AutoCompleteStringCollection();

                    conn.Open();
                    dr = cmd.ExecuteReader();
                    if (dr.HasRows == false)
                    {

                        AddSpareParts objAddSpareParts = new AddSpareParts(dgvInvoiceSummery, dgvInvoiceSummery.CurrentRow.Index, partsNo);
                        objAddSpareParts.ShowDialog();

                    }
                    while (dr.Read())
                    {
                        dgvInvoiceSummery.Rows[e.RowIndex].Cells[1].Value = dr.GetString(0);
                    }
                    conn.Close();
                }
                int itemQuantity = 0;
                decimal unitPrice = 0;
                decimal totalPrice = 0;

                if (e.ColumnIndex == 2 || e.ColumnIndex == 3 || e.ColumnIndex == 4)
                {
                    if (Convert.ToString(dgvInvoiceSummery.Rows[e.RowIndex].Cells[2].Value) != "")
                    {
                        itemQuantity = Convert.ToInt32(dgvInvoiceSummery.Rows[e.RowIndex].Cells[2].Value);
                    }

                    if (Convert.ToString(dgvInvoiceSummery.Rows[e.RowIndex].Cells[3].Value) != "")
                    {
                        unitPrice = Convert.ToDecimal(dgvInvoiceSummery.Rows[e.RowIndex].Cells[3].Value);
                        totalPrice = decimal.Round(itemQuantity * unitPrice, 2);
                    }
                    if (totalPrice == 0)
                        dgvInvoiceSummery.Rows[e.RowIndex].Cells[4].Value = "";
                    else
                        dgvInvoiceSummery.Rows[e.RowIndex].Cells[4].Value = totalPrice;

                }

            }
            catch (Exception ex)
            {

            }
            finally
            {

            }
        }

        private void pbDelete_Click(object sender, EventArgs e)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to delete this invoice?", "Delete Invoice", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (dr==DialogResult.Yes)
            {
            DataTable dt = (DataTable)dgvInvoiceSummery.DataSource;

                string query;
                int rowsAffacted = 0;
                DataAccess da = new DataAccess();
                da.openConnection();
                SqlTransaction trans = da.conn.BeginTransaction();

                query = "DELETE FROM SpareSalesRegister WHERE InvoiceNo='" + originalInvoiceNo + "'";
                if (da.DoCommand(query, trans) == 0)
                    IsTransCommitable = false;
              
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string partsNo = "";
                    int originalQty = 0;
                    decimal unitPrice = 0;
                    decimal TotalPrice = 0;
                    int inventoryId=0;

                    partsNo = dt.Rows[i]["Part Number", DataRowVersion.Original].ToString();
                    originalQty = Convert.ToInt32(dt.Rows[i]["Quantity", DataRowVersion.Original]);
                    unitPrice = Convert.ToInt32(dt.Rows[i]["Unit Price", DataRowVersion.Original]);
                    TotalPrice = Convert.ToInt32(dt.Rows[i]["Total", DataRowVersion.Original]);
                    inventoryId = Convert.ToInt32(dt.Rows[i]["InventoryId", DataRowVersion.Original]);

                    /// Update Inventory
                    int availableQty = GetAvailableQty(da, partsNo,trans);
                    if (availableQty != -1)// If not Null
                    {
                        query = "UPDATE InventoryMaster SET isCurrent=0 WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
                        da.DoCommand(query,trans);
                    }
                    else
                    {
                        availableQty = 0;
                    }
                    SqlParameter[] param1 = new SqlParameter[9];
                    param1[0] = new SqlParameter("PartsNo", partsNo);
                    param1[1] = new SqlParameter("Qty", originalQty);
                    param1[2] = new SqlParameter("AvailableQty", availableQty + originalQty);
                    param1[3] = new SqlParameter("TransactionDate", DateTime.Now);
                    param1[4] = new SqlParameter("isCurrent", 1);
                    param1[5] = new SqlParameter("TransactionType", Enums.TransactionType.SalesItemDelete);
                    param1[6] = new SqlParameter("InvoiceNo", txtSalesInvoiceNo.Text);
                    param1[7] = new SqlParameter("UnitPrice", unitPrice);
                    param1[8] = new SqlParameter("TotalPrice", TotalPrice);

                    //query = "INSERT INTO InventoryMaster (PartsNo, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                    //rowsAffacted = da.DoCommand(query, param1);

                    query = "DELETE FROM InventoryMaster WHERE InventoryId=" + inventoryId;
                    ////query = "INSERT INTO InventoryMaster (PartsNo, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                    if (da.DoCommand(query,trans)==0)
                        IsTransCommitable = false;

                    query = "INSERT INTO InventoryMaster (PartsNO, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                    if (da.DoCommand(query, param1, trans) == 0)
                        IsTransCommitable = false;

                }

                try
                {
                    if (IsTransCommitable)
                        trans.Commit();
                    else
                        trans.Rollback();
                }
                catch { }
                finally
                {
                    da.conn.Close();
                }

                this.Close();
                // Show Register window if already opened
                Registers Registers;
                if (General.isOpened("Registers"))
                {
                    Registers = (Registers)Application.OpenForms["Registers"];
                    Registers.Close();
                    Registers = new Registers();
                    Registers.MdiParent = DashBoard.ActiveForm;
                    Registers.Show();
                    Registers.ActivateSales(sender, e);
                }
            }

        }
    }
}